﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Healthy
{
    public partial class frmschool : Form
    {
        public frmschool()
        {
            InitializeComponent();
        }
        int num;
        private void frmschool_Load(object sender, EventArgs e)
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);

        }
        private void showdata4()
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();

            string sql = "SELECT * FROM school ";
            SqlCommand cmd = new SqlCommand(sql, Conn);
            SqlDataAdapter odbcDA = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();

            //Conn.Open();
            odbcDA.Fill(ds);
            //cmd.ExecuteReader();

            this.dgvSchool.DataSource = ds.Tables[0];
            DataGridViewCellStyle cs = new DataGridViewCellStyle();
            cs.Font = new Font("Ms Sans Serif", 10, FontStyle.Bold);
            this.dgvSchool.ColumnHeadersDefaultCellStyle = cs;
            this.dgvSchool.Columns[0].HeaderText = "รหัสโรงเรียน";
            this.dgvSchool.Columns[1].HeaderText = "ชื่อโรงเรียน";
            this.dgvSchool.Columns[2].HeaderText = "ที่อยู่";


            this.dgvSchool.Columns[0].Width = 100;
            this.dgvSchool.Columns[1].Width = 200;
            this.dgvSchool.Columns[2].Width = 250;
        }
        private void Format4()
        {
            DataGridViewCellStyle cs = new DataGridViewCellStyle();
            cs.Font = new Font("Ms Sans Serif", 10, FontStyle.Bold);
            this.dgvSchool.ColumnHeadersDefaultCellStyle = cs;
            this.dgvSchool.Columns[0].HeaderText = "รหัสโรงเรียน";
            this.dgvSchool.Columns[1].HeaderText = "ชื่อโรงเรียน";
            this.dgvSchool.Columns[2].HeaderText = "ที่อยู่";


            this.dgvSchool.Columns[0].Width = 100;
            this.dgvSchool.Columns[1].Width = 200;
            this.dgvSchool.Columns[2].Width = 250;

            dgvSchool.Rows[dgvSchool.Rows.Count - 1].Height = 0;

        }
        private void ShowSch()
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();

            string sql = "select * from school Where SCHID = '" + schoolID.Text + "'";
            SqlCommand cmd1 = new SqlCommand(sql, Conn);
            SqlDataAdapter odbcDA = new SqlDataAdapter(cmd1);
            DataSet ds = new DataSet();

            odbcDA.Fill(ds, "school");
            num = ds.Tables["school"].Rows.Count;

            DataGridViewCellStyle cs = new DataGridViewCellStyle();
            cs.Font = new Font("Ms Sans Serif", 10, FontStyle.Bold);
            this.dgvSchool.ColumnHeadersDefaultCellStyle = cs;
            this.dgvSchool.Columns[0].HeaderText = "รหัสโรงเรียน";
            this.dgvSchool.Columns[1].HeaderText = "ชื่อโรงเรียน";
            this.dgvSchool.Columns[2].HeaderText = "ที่อยู่";


            this.dgvSchool.Columns[0].Width = 100;
            this.dgvSchool.Columns[1].Width = 200;
            this.dgvSchool.Columns[2].Width = 250;
        }
        private void insert_Click(object sender, EventArgs e)
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();

           

            string query = "insert into school values('" + schoolID.Text + "' , '" + schoolName.Text + "','" + schoolAddress.Text + "' )";
            SqlCommand cmd7 = new SqlCommand(query, Conn);
            cmd7.ExecuteNonQuery();

            string sql = "select * from school Where SCHID= '" + schoolID.Text + "' ";
            SqlCommand cmd1 = new SqlCommand(sql, Conn);
            SqlDataAdapter odbcDA = new SqlDataAdapter(cmd1);
            DataSet ds = new DataSet();

            //Conn.Open();
            odbcDA.Fill(ds);
            //cmd.ExecuteReader();


            this.dgvSchool.DataSource = ds.Tables[0];

            DataGridViewCellStyle cs = new DataGridViewCellStyle();
            cs.Font = new Font("Ms Sans Serif", 10, FontStyle.Bold);
            this.dgvSchool.ColumnHeadersDefaultCellStyle = cs;
            this.dgvSchool.Columns[0].HeaderText = "รหัสโรงเรียน";
            this.dgvSchool.Columns[1].HeaderText = "ชื่อโรงเรียน";
            this.dgvSchool.Columns[2].HeaderText = "ที่อยู่";


            this.dgvSchool.Columns[0].Width = 100;
            this.dgvSchool.Columns[1].Width = 200;
            this.dgvSchool.Columns[2].Width = 250;
            
        }

        private void update_Click(object sender, EventArgs e)
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();

            if (schoolID.Text == "" || schoolName.Text == "" || schoolAddress.Text == "")
            {
                MessageBox.Show("กรุณาป้อนข้อมูลให้ครบ!!!", "ข้อผิดพลาด", MessageBoxButtons.OK, MessageBoxIcon.Information);
                schoolID.Focus();
                schoolID.SelectAll();
                return;
            }
            string sqlupdate = "update school set SCHID='" + schoolID.Text + "',SCHNAM='" + schoolName.Text + "',ADDRESS='" + schoolAddress.Text + "' where SCHID='" + schoolID.Text + "'";
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sqlupdate;
            cmd.Connection = Conn;
            cmd.ExecuteNonQuery();
            ShowSch();

            schoolID.Text = "";
            schoolName.Text = "";
            schoolAddress.Text = "";
        }

        private void delete_Click(object sender, EventArgs e)
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();

            string query = "delete from school where SCHID= '" + schoolID.Text + "'";
            SqlCommand cmd = new SqlCommand(query, Conn);
            cmd.ExecuteNonQuery();

            string sql = "SELECT * FROM school ";
            SqlCommand cmd1 = new SqlCommand(sql, Conn);
            SqlDataAdapter odbcDA = new SqlDataAdapter(cmd1);
            DataSet ds = new DataSet();

            //Conn.Open();
            odbcDA.Fill(ds);
            //cmd.ExecuteReader();
            this.dgvSchool.DataSource = ds.Tables[0];

            DataGridViewCellStyle cs = new DataGridViewCellStyle();
            cs.Font = new Font("Ms Sans Serif", 10, FontStyle.Bold);
            this.dgvSchool.ColumnHeadersDefaultCellStyle = cs;
            this.dgvSchool.Columns[0].HeaderText = "รหัสโรงเรียน";

            this.dgvSchool.Columns[1].HeaderText = "ชื่อโรงเรียน";
            this.dgvSchool.Columns[2].HeaderText = "ที่อยู่";


            this.dgvSchool.Columns[0].Width = 100;
            this.dgvSchool.Columns[1].Width = 200;
            this.dgvSchool.Columns[2].Width = 250;
        }

        private void button2_Click(object sender, EventArgs e)
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();

            string sql = "SELECT * FROM school ";
            SqlCommand cmd = new SqlCommand(sql, Conn);
            SqlDataAdapter odbcDA = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();

            //Conn.Open();
            odbcDA.Fill(ds);
            //cmd.ExecuteReader();
            this.dgvSchool.DataSource = ds.Tables[0];

            DataGridViewCellStyle cs = new DataGridViewCellStyle();
            cs.Font = new Font("MS Sans Serif", 14, FontStyle.Regular);


            this.dgvSchool.ColumnHeadersDefaultCellStyle = cs;
            this.dgvSchool.Columns[0].HeaderText = "รหัสโรงเรียน";
            this.dgvSchool.Columns[1].HeaderText = "ชื่อโรงเรียน";
            this.dgvSchool.Columns[2].HeaderText = "ที่อยู่";


            this.dgvSchool.Columns[0].Width = 100;
            this.dgvSchool.Columns[1].Width = 200;
            this.dgvSchool.Columns[2].Width = 250;

        }

        private void button1_Click(object sender, EventArgs e)
        {
            Close();
        }

        private void dgvSchool_CellMouseUp(object sender, DataGridViewCellMouseEventArgs e)
        {
            schoolID.Text = Convert.ToString(dgvSchool.Rows[e.RowIndex].Cells[0].Value);
            schoolName.Text = Convert.ToString(dgvSchool.Rows[e.RowIndex].Cells[1].Value);
            schoolAddress.Text = Convert.ToString(dgvSchool.Rows[e.RowIndex].Cells[2].Value);
        }
    }
}
